"""
Oracle 数据库管理类

该模块提供了 Oracle 数据库的连接管理和常用操作功能，包括查询执行、数据插入、更新、删除等。
"""
import oracledb
from typing import Dict, List, Tuple, Optional, Any
from modules.logger import LogManager


class OracleManager:
    """
    Oracle 数据库管理类，封装了常用的数据库操作方法
    """
    def __init__(self,
                 host: str = 'localhost',
                 service_name: str = 'ORCLCDB',
                 user: str = 'sys',
                 password: str = 'Oradoc_db1',
                 port: int = 1521,
                 as_sysdba: bool = False,
                 logger_name: str = 'oracle_manager'):
        """
        初始化 Oracle 连接参数

        Args:
            host: 服务器地址
            service_name: 服务名称
            user: 登录用户名
            password: 登录密码
            port: 数据库端口
            as_sysdba: 是否以 SYSDBA 身份连接
            logger_name: 日志记录器名称
        """
        self.host = host
        self.service_name = service_name
        self.user = user
        self.password = password
        self.port = port
        self.as_sysdba = as_sysdba
        self.connection: Optional[oracledb.Connection] = None
        self.cursor: Optional[oracledb.Cursor] = None
        
        # 初始化日志
        self.logger = LogManager.get_both_logger(name=logger_name)

    def connect(self) -> bool:
        """
        建立数据库连接

        Returns:
            bool: 连接成功返回 True，失败返回 False
        """
        try:
            dsn = oracledb.makedsn(self.host, self.port, service_name=self.service_name)
            mode = oracledb.AUTH_MODE_SYSDBA if self.as_sysdba else None
            
            self.connection = oracledb.connect(
                user=self.user,
                password=self.password,
                dsn=dsn,
                mode=mode
            )
            
            self.cursor = self.connection.cursor()
            self.logger.info(f"成功连接到 Oracle: {self.host}:{self.port}/{self.service_name}")
            return True
        except oracledb.Error as e:
            self.logger.error(f"数据库连接失败: {str(e)}", exc_info=True)
            self.connection = None
            self.cursor = None
            return False

    def close(self) -> None:
        """
        关闭数据库连接
        """
        if self.cursor:
            try:
                self.cursor.close()
            except oracledb.Error as e:
                self.logger.warning(f"关闭游标时出错: {str(e)}")
            finally:
                self.cursor = None

        if self.connection:
            try:
                self.connection.close()
                self.logger.info(f"已关闭与 {self.host}:{self.port}/{self.service_name} 的连接")
            except oracledb.Error as e:
                self.logger.warning(f"关闭连接时出错: {str(e)}")
            finally:
                self.connection = None

    def _ensure_connection(self) -> bool:
        """
        确保数据库连接处于打开状态

        Returns:
            bool: 连接有效返回 True，否则返回 False
        """
        if not self.connection or self.connection.isclosed():
            return self.connect()
        return True

    def execute_query(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> List[Dict[str, Any]]:
        """
        执行查询语句并返回结果

        Args:
            query: SQL 查询语句
            params: 查询参数（用于参数化查询）

        Returns:
            List[Dict[str, Any]]: 查询结果列表，每个元素为一行数据的字典
        """
        if not self._ensure_connection():
            self.logger.error("无法执行查询，数据库连接失败")
            return []

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)

            # 获取列名
            columns = [col[0] for col in self.cursor.description]
            # 获取结果并转换为字典列表
            results = [dict(zip(columns, row)) for row in self.cursor.fetchall()]
            
            self.logger.info(f"查询执行成功，返回 {len(results)} 条记录")
            return results
        except oracledb.Error as e:
            self.logger.error(f"查询执行失败: {str(e)}", exc_info=True)
            return []

    def execute_non_query(self, query: str, params: Optional[Tuple[Any, ...]] = None) -> int:
        """
        执行非查询语句（INSERT, UPDATE, DELETE 等）

        Args:
            query: SQL 语句
            params: 查询参数（用于参数化查询）

        Returns:
            int: 受影响的行数，失败返回 -1
        """
        if not self._ensure_connection():
            self.logger.error("无法执行命令，数据库连接失败")
            return -1

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            
            self.connection.commit()
            affected_rows = self.cursor.rowcount
            self.logger.info(f"命令执行成功，影响 {affected_rows} 行")
            return affected_rows
        except oracledb.Error as e:
            self.connection.rollback()
            self.logger.error(f"命令执行失败: {str(e)}", exc_info=True)
            return -1

    def __enter__(self):
        """上下文管理器入口，用于 with 语句"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器出口，用于 with 语句"""
        self.close()
        if exc_type:
            self.logger.error(f"数据库操作发生异常: {exc_val}", exc_info=(exc_type, exc_val, exc_tb))
        return False

    def insert(self, table_name: str, data: Dict[str, Any]) -> int:
        """
        插入单行数据到指定表

        Args:
            table_name: 目标表名
            data: 字典形式的列名-值对

        Returns:
            int: 受影响的行数，失败返回 -1
        """
        if not data:
            self.logger.warning("插入数据为空，不执行操作")
            return 0

        columns = [f"{col}" for col in data.keys()]
        placeholders = [f":{col}" for col in data.keys()]
        sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(placeholders)})"
        
        return self.execute_non_query(sql, data)

    def batch_insert(self, table_name: str, data_list: List[Dict[str, Any]], batch_size: int = 1000) -> int:
        """
        批量插入多行数据到指定表

        Args:
            table_name: 目标表名
            data_list: 字典列表，每个字典为一行数据
            batch_size: 每批插入的记录数

        Returns:
            int: 受影响的行数，失败返回 -1
        """
        if not data_list:
            self.logger.warning("批量插入数据为空，不执行操作")
            return 0

        # 确保所有字典的键相同
        columns = [f"{col}" for col in data_list[0].keys()]
        placeholders = [f":{col}" for col in data_list[0].keys()]
        sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(placeholders)})"
        
        total_affected = 0
        try:
            if not self._ensure_connection():
                self.logger.error("无法执行批量插入，数据库连接失败")
                return -1
                
            # 分批处理
            for i in range(0, len(data_list), batch_size):
                batch = data_list[i:i+batch_size]
                self.cursor.executemany(sql, batch)
                self.connection.commit()
                affected = self.cursor.rowcount
                total_affected += affected
                self.logger.info(f"已插入 {affected} 条记录，累计 {total_affected} 条")
                
            self.logger.info(f"批量插入完成，共影响 {total_affected} 行")
            return total_affected
        except oracledb.Error as e:
            self.connection.rollback()
            self.logger.error(f"批量插入失败: {str(e)}", exc_info=True)
            return -1

    def update(self, table_name: str, set_data: Dict[str, Any], where_clause: str, where_params: Optional[Dict[str, Any]] = None) -> int:
        """
        更新指定表中的数据

        Args:
            table_name: 目标表名
            set_data: 字典形式的列名-值对，表示要更新的字段和值
            where_clause: WHERE 子句，指定更新条件
            where_params: WHERE 子句的参数

        Returns:
            int: 受影响的行数，失败返回 -1
        """
        if not set_data:
            self.logger.warning("更新数据为空，不执行操作")
            return 0

        set_clause = ", ".join([f"{col} = :{col}" for col in set_data.keys()])
        sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
        
        # 合并 SET 和 WHERE 的参数
        params = set_data.copy()
        if where_params:
            params.update(where_params)
        
        return self.execute_non_query(sql, params)

    def delete(self, table_name: str, where_clause: str, where_params: Optional[Dict[str, Any]] = None) -> int:
        """
        从指定表中删除数据

        Args:
            table_name: 目标表名
            where_clause: WHERE 子句，指定删除条件
            where_params: WHERE 子句的参数

        Returns:
            int: 受影响的行数，失败返回 -1
        """
        sql = f"DELETE FROM {table_name} WHERE {where_clause}"
        return self.execute_non_query(sql, where_params)

    def execute_stored_procedure(self, proc_name: str, params: Optional[Dict[str, Any]] = None) -> List[List[Dict[str, Any]]]:
        """
        执行存储过程

        Args:
            proc_name: 存储过程名称
            params: 存储过程参数（字典形式，key为参数名）

        Returns:
            List[List[Dict[str, Any]]]: 存储过程返回的结果集列表
        """
        if not self._ensure_connection():
            self.logger.error("无法执行存储过程，数据库连接失败")
            return []

        try:
            # 准备参数
            if params:
                for key, value in params.items():
                    self.cursor.setinputsizes(**{key: oracledb.DB_TYPE_VARCHAR})
                    self.cursor.bindvar(key, value)
                
                # 构建调用语句
                param_names = ", ".join([f":{key}" for key in params.keys()]) if params else ""
                sql = f"BEGIN {proc_name}({param_names}); END;"
            else:
                sql = f"BEGIN {proc_name}; END;"

            self.cursor.execute(sql)
            
            results = []
            # 处理多个结果集
            while True:
                try:
                    # 获取列名
                    columns = [col[0] for col in self.cursor.description]
                    # 获取结果并转换为字典列表
                    result = [dict(zip(columns, row)) for row in self.cursor.fetchall()]
                    if result:
                        results.append(result)
                except oracledb.InterfaceError:
                    # 没有更多结果集
                    break
                
                # 移动到下一个结果集
                if not self.cursor.nextset():
                    break
                    
            self.logger.info(f"存储过程 {proc_name} 执行成功")
            return results
        except oracledb.Error as e:
            self.logger.error(f"存储过程执行失败: {str(e)}", exc_info=True)
            return []

    def begin_transaction(self) -> None:
        """
        开始事务
        将自动提交设置为False，需要手动调用commit()或rollback()
        """
        if self._ensure_connection():
            self.connection.autocommit = False
            self.logger.info("事务已开始")

    def commit(self) -> None:
        """
        提交事务
        """
        if self.connection and not self.connection.autocommit:
            self.connection.commit()
            self.logger.info("事务已提交")
            self.connection.autocommit = True

    def rollback(self) -> None:
        """
        回滚事务
        """
        if self.connection and not self.connection.autocommit:
            self.connection.rollback()
            self.logger.info("事务已回滚")
            self.connection.autocommit = True

    def get_table_schema(self, table_name: str, owner: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取表结构信息

        Args:
            table_name: 表名
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            List[Dict[str, Any]]: 包含列信息的字典列表
        """
        query = """
        SELECT
            COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, DATA_LENGTH
        FROM
            ALL_TAB_COLUMNS
        WHERE
            TABLE_NAME = :table_name
            {owner_clause}
        ORDER BY
            COLUMN_ID
        """
        
        params = {'table_name': table_name.upper()}
        owner_clause = "AND OWNER = :owner" if owner else "AND OWNER = USER"
        if owner:
            params['owner'] = owner.upper()
        
        formatted_query = query.format(owner_clause=owner_clause)
        return self.execute_query(formatted_query, params)

    def is_table_exists(self, table_name: str, owner: Optional[str] = None) -> bool:
        """
        检查表是否存在

        Args:
            table_name: 表名
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            bool: 表存在返回True，否则返回False
        """
        query = """
        SELECT COUNT(*) AS TableCount
        FROM ALL_TABLES
        WHERE TABLE_NAME = :table_name
            {owner_clause}
        """
        
        params = {'table_name': table_name.upper()}
        owner_clause = "AND OWNER = :owner" if owner else "AND OWNER = USER"
        if owner:
            params['owner'] = owner.upper()
        
        formatted_query = query.format(owner_clause=owner_clause)
        result = self.execute_query(formatted_query, params)
        return result[0]['TABLECOUNT'] > 0 if result else False

    def is_column_exists(self, table_name: str, column_name: str, owner: Optional[str] = None) -> bool:
        """
        检查列是否存在于表中

        Args:
            table_name: 表名
            column_name: 列名
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            bool: 列存在返回True，否则返回False
        """
        query = """
        SELECT COUNT(*) AS ColumnCount
        FROM ALL_TAB_COLUMNS
        WHERE TABLE_NAME = :table_name
            AND COLUMN_NAME = :column_name
            {owner_clause}
        """
        
        params = {
            'table_name': table_name.upper(),
            'column_name': column_name.upper()
        }
        owner_clause = "AND OWNER = :owner" if owner else "AND OWNER = USER"
        if owner:
            params['owner'] = owner.upper()
        
        formatted_query = query.format(owner_clause=owner_clause)
        result = self.execute_query(formatted_query, params)
        return result[0]['COLUMNCOUNT'] > 0 if result else False

    def is_stored_procedure_exists(self, proc_name: str, owner: Optional[str] = None) -> bool:
        """
        检查存储过程是否存在

        Args:
            proc_name: 存储过程名称
            owner: 存储过程所有者（模式名），默认为当前用户

        Returns:
            bool: 存储过程存在返回True，否则返回False
        """
        query = """
        SELECT COUNT(*) AS ProcCount
        FROM ALL_PROCEDURES
        WHERE OBJECT_NAME = :proc_name
            AND OBJECT_TYPE = 'PROCEDURE'
            {owner_clause}
        """
        
        params = {'proc_name': proc_name.upper()}
        owner_clause = "AND OWNER = :owner" if owner else "AND OWNER = USER"
        if owner:
            params['owner'] = owner.upper()
        
        formatted_query = query.format(owner_clause=owner_clause)
        result = self.execute_query(formatted_query, params)
        return result[0]['PROCOUNT'] > 0 if result else False

    def bulk_upsert(self, table_name: str, data_list: List[Dict[str, Any]], unique_columns: List[str], batch_size: int = 1000, owner: Optional[str] = None) -> int:
        """
        批量 upsert 数据（插入或更新）

        Args:
            table_name: 目标表名
            data_list: 字典列表，每个字典为一行数据
            unique_columns: 用于判断重复的唯一列列表
            batch_size: 每批处理的记录数
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            int: 受影响的行数，失败返回-1
        """
        if not data_list:
            self.logger.warning("批量upsert数据为空，不执行操作")
            return 0

        owner_prefix = f"{owner}." if owner else ""
        all_columns = [col for col in data_list[0].keys()]
        update_columns = [col for col in all_columns if col not in unique_columns]

        # 构建 MERGE 语句
        sql = f"""
        MERGE INTO {owner_prefix}{table_name} target
        USING (
            SELECT {', '.join([f':{col}_src AS {col}' for col in all_columns])}
            FROM DUAL
        ) source
        ON ({' AND '.join([f'target.{col} = source.{col}' for col in unique_columns])})
        WHEN MATCHED THEN
            UPDATE SET {', '.join([f'{col} = source.{col}' for col in update_columns])}
        WHEN NOT MATCHED THEN
            INSERT ({', '.join(all_columns)})
            VALUES ({', '.join([f'source.{col}' for col in all_columns])});
        """

        total_affected = 0
        try:
            if not self._ensure_connection():
                self.logger.error("无法执行批量upsert，数据库连接失败")
                return -1

            for i in range(0, len(data_list), batch_size):
                batch = data_list[i:i+batch_size]
                # 为每批数据准备参数（添加_src后缀以避免与目标表列名冲突）
                batch_params = []
                for row in batch:
                    param = {f'{k}_src': v for k, v in row.items()}
                    batch_params.append(param)
                
                self.cursor.executemany(sql, batch_params)
                self.connection.commit()
                affected = self.cursor.rowcount
                total_affected += affected
                self.logger.info(f"已upsert {affected} 条记录，累计 {total_affected} 条")

            self.logger.info(f"批量upsert完成，共影响 {total_affected} 行")
            return total_affected
        except oracledb.Error as e:
            self.connection.rollback()
            self.logger.error(f"批量upsert失败: {str(e)}", exc_info=True)
            return -1

    def create_index(self, table_name: str, index_name: str, columns: List[str], is_unique: bool = False, owner: Optional[str] = None) -> bool:
        """
        创建索引

        Args:
            table_name: 表名
            index_name: 索引名称
            columns: 索引列列表
            is_unique: 是否唯一索引
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            bool: 创建成功返回True
        """
        if not columns:
            self.logger.warning("索引列列表不能为空")
            return False

        owner_prefix = f"{owner}." if owner else ""
        unique_clause = "UNIQUE" if is_unique else ""
        columns_str = ", ".join(columns)
        sql = f"CREATE {unique_clause} INDEX {index_name} ON {owner_prefix}{table_name} ({columns_str})"
        return self.execute_non_query(sql) > 0

    def drop_index(self, index_name: str, owner: Optional[str] = None) -> bool:
        """
        删除索引

        Args:
            index_name: 索引名称
            owner: 索引所有者（模式名），默认为当前用户

        Returns:
            bool: 删除成功返回True
        """
        owner_prefix = f"{owner}." if owner else ""
        sql = f"DROP INDEX {owner_prefix}{index_name}"
        return self.execute_non_query(sql) > 0

    def get_indexes(self, table_name: str, owner: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取表的索引信息

        Args:
            table_name: 表名
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            List[Dict[str, Any]]: 索引信息列表
        """
        query = """
        SELECT
            i.INDEX_NAME AS index_name,
            ic.COLUMN_NAME AS column_name,
            DECODE(i.UNIQUENESS, 'UNIQUE', 'N', 'Y') AS is_non_unique,
            ic.COLUMN_POSITION AS sequence_in_index,
            i.INDEX_TYPE AS index_type
        FROM
            ALL_INDEXES i
        JOIN
            ALL_IND_COLUMNS ic ON i.INDEX_NAME = ic.INDEX_NAME AND i.TABLE_OWNER = ic.INDEX_OWNER
        WHERE
            i.TABLE_NAME = :table_name
            {owner_clause}
            AND i.INDEX_NAME NOT LIKE 'SYS_%'
        ORDER BY
            i.INDEX_NAME, ic.COLUMN_POSITION
        """
        
        params = {'table_name': table_name.upper()}
        owner_clause = "AND i.TABLE_OWNER = :owner" if owner else "AND i.TABLE_OWNER = USER"
        if owner:
            params['owner'] = owner.upper()
        
        formatted_query = query.format(owner_clause=owner_clause)
        return self.execute_query(formatted_query, params)

    def bulk_update(self, table_name: str, update_data_list: List[Tuple[Dict[str, Any], str, Dict[str, Any]]], batch_size: int = 1000, owner: Optional[str] = None) -> int:
        """
        批量更新数据

        Args:
            table_name: 目标表名
            update_data_list: 更新数据列表，每个元素为元组(更新数据字典, where条件, where参数)
            batch_size: 每批更新的记录数
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            int: 受影响的总行数
        """
        if not update_data_list:
            self.logger.warning("批量更新数据为空，不执行操作")
            return 0

        owner_prefix = f"{owner}." if owner else ""
        total_affected = 0
        try:
            if not self._ensure_connection():
                self.logger.error("无法执行批量更新，数据库连接失败")
                return -1

            # 分批处理
            for i in range(0, len(update_data_list), batch_size):
                batch = update_data_list[i:i+batch_size]
                self.begin_transaction()
                batch_affected = 0

                for set_data, where_clause, where_params in batch:
                    set_clause = ", ".join([f"{col} = :{col}" for col in set_data.keys()])
                    sql = f"UPDATE {owner_prefix}{table_name} SET {set_clause} WHERE {where_clause}"
                    
                    # 合并 SET 和 WHERE 的参数
                    params = set_data.copy()
                    if where_params:
                        params.update(where_params)
                    
                    self.cursor.execute(sql, params)
                    batch_affected += self.cursor.rowcount

                self.commit()
                total_affected += batch_affected
                self.logger.info(f"已更新 {batch_affected} 条记录，累计 {total_affected} 条")

            self.logger.info(f"批量更新完成，共影响 {total_affected} 行")
            return total_affected
        except oracledb.Error as e:
            self.connection.rollback()
            self.logger.error(f"批量更新失败: {str(e)}", exc_info=True)
            return -1

    def bulk_delete(self, table_name: str, where_clauses: List[Tuple[str, Dict[str, Any]]], batch_size: int = 1000, owner: Optional[str] = None) -> int:
        """
        批量删除数据

        Args:
            table_name: 目标表名
            where_clauses: 删除条件列表，每个元素为元组(where条件, where参数)
            batch_size: 每批删除的记录数
            owner: 表所有者（模式名），默认为当前用户

        Returns:
            int: 受影响的总行数
        """
        if not where_clauses:
            self.logger.warning("批量删除条件为空，不执行操作")
            return 0

        owner_prefix = f"{owner}." if owner else ""
        total_affected = 0
        try:
            if not self._ensure_connection():
                self.logger.error("无法执行批量删除，数据库连接失败")
                return -1

            # 分批处理
            for i in range(0, len(where_clauses), batch_size):
                batch = where_clauses[i:i+batch_size]
                self.begin_transaction()
                batch_affected = 0

                for where_clause, where_params in batch:
                    sql = f"DELETE FROM {owner_prefix}{table_name} WHERE {where_clause}"
                    self.cursor.execute(sql, where_params)
                    batch_affected += self.cursor.rowcount

                self.commit()
                total_affected += batch_affected
                self.logger.info(f"已删除 {batch_affected} 条记录，累计 {total_affected} 条")

            self.logger.info(f"批量删除完成，共影响 {total_affected} 行")
            return total_affected
        except oracledb.Error as e:
            self.connection.rollback()
            self.logger.error(f"批量删除失败: {str(e)}", exc_info=True)
            return -1